package com.dao;

import com.model.Exchange;
import com.model.PageBean;
import com.util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wangjg
 * @date 2023/11/1 15:15
 * @Description: 调换申请处理DAO
 */
public class ExchangeDao {

    public List<Exchange> exchangeList(Connection con, PageBean pageBean, Exchange exchange)throws Exception {
        List<Exchange> exchangeList = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select * from t_exchange t1  where 1=1");
        if(StringUtil.isNotEmpty(exchange.getUserName())) {
            sb.append(" and t1.userName like '%").append(exchange.getUserName()).append("%'");
        }
        if(null != exchange.getUserId()){
            sb.append(" and t1.userId = ").append(exchange.getUserId());
        }
        if(pageBean != null) {
            sb.append(" limit ").append(pageBean.getStart()).append(",").append(pageBean.getPageSize());
        }
        PreparedStatement preparedStatement = con.prepareStatement(sb.toString());
        ResultSet rs = preparedStatement.executeQuery();
        while(rs.next()) {
            Exchange exchange1 = new Exchange();
            exchange1.setId(rs.getInt("id"));
            exchange1.setUserId(rs.getInt("userId"));
            exchange1.setUserName(rs.getString("userName"));
            exchange1.setSex(rs.getString("sex"));
            exchange1.setBuildId(rs.getInt("buildId"));
            exchange1.setRoomId(rs.getInt("roomId"));
            exchange1.setReason(rs.getString("reason"));
            exchange1.setDate(rs.getString("date"));
            exchange1.setStatus(rs.getString("status"));
            exchange1.setRoomNum(rs.getString("roomNum"));
            exchange1.setBuildName(rs.getString("buildName"));
            exchangeList.add(exchange1);
        }
        return exchangeList;
    }

    public Exchange exchangeInfo(Connection con, int id)throws Exception {
        String sql = "select * from t_exchange where id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        ResultSet rs = preparedStatement.executeQuery();
        if(rs.next()) {
            Exchange exchange = new Exchange();
            exchange.setId(rs.getInt("id"));
            exchange.setUserId(rs.getInt("userId"));
            exchange.setUserName(rs.getString("userName"));
            exchange.setSex(rs.getString("sex"));
            exchange.setBuildId(rs.getInt("buildId"));
            exchange.setRoomId(rs.getInt("roomId"));
            exchange.setReason(rs.getString("reason"));
            exchange.setDate(rs.getString("date"));
            exchange.setStatus(rs.getString("status"));
            exchange.setRoomNum(rs.getString("roomNum"));
            exchange.setBuildName(rs.getString("buildName"));
            return exchange;
        }
        return null;
    }

    public int exchangeCount(Connection con, Exchange exchange)throws Exception {
        StringBuilder sb = new StringBuilder("select count(*) as total from t_exchange t1");
        if(StringUtil.isNotEmpty(exchange.getUserName())) {
            sb.append(" where t1.userName like '%").append(exchange.getUserName()).append("%'");
        }
        PreparedStatement preparedStatement = con.prepareStatement(sb.toString());
        ResultSet rs = preparedStatement.executeQuery();
        if(rs.next()) {
            return rs.getInt("total");
        } else {
            return 0;
        }
    }


    public int addExchange(Connection con, Exchange exchange)throws Exception {
        String sql = "insert into t_exchange values(null,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setInt(1, exchange.getUserId());
        preparedStatement.setString(2, exchange.getUserName());
        preparedStatement.setString(3, exchange.getSex());
        preparedStatement.setInt(4, exchange.getBuildId());
        preparedStatement.setString(5,exchange.getBuildName());
        preparedStatement.setInt(6, exchange.getRoomId());
        preparedStatement.setString(7, exchange.getRoomNum());
        preparedStatement.setString(8, exchange.getReason());
        preparedStatement.setString(9, exchange.getDate());
        preparedStatement.setString(10, exchange.getStatus());
        return preparedStatement.executeUpdate();
    }

    public int deleteExchange(Connection con, String id)throws Exception {
        String sql = "delete from t_exchange where id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1, id);
        return preparedStatement .executeUpdate();
    }

    public int updateExchange(Connection con, Exchange exchange)throws Exception {
        String sql = "update t_exchange set userId=?,userName=? ,sex=? ,buildId=?,buildName=?,roomId=?,roomNum=?,reason=?,date=?,status=? where id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setInt(1, exchange.getUserId());
        preparedStatement.setString(2, exchange.getUserName());
        preparedStatement.setString(3, exchange.getSex());
        preparedStatement.setInt(4, exchange.getBuildId());
        preparedStatement.setString(5, exchange.getBuildName());
        preparedStatement.setInt(6, exchange.getRoomId());
        preparedStatement.setString(7, exchange.getRoomNum());
        preparedStatement.setString(8, exchange.getReason());
        preparedStatement.setString(9, exchange.getDate());
        preparedStatement.setString(10, exchange.getStatus());
        preparedStatement.setInt(11, exchange.getId());
        return preparedStatement.executeUpdate();
    }
}
